<?php
/**
 * Created by PhpStorm.
 * User: a
 * Date: 2016/6/1
 * Time: 10:12
 */
namespace Admin\Model;
use Think\Model;
class CustinfoModel extends Model{
    protected $trueTableName = 'bypal_cust_info';

    public function get_info($custid){
        $values=$this->where("cust_id='$custid'")->select();
        return $values;
    }

    //查询客户基本信息
    public function get_baisc_by_id($cust_id){
        $sql="select cust_name,cust_sex,cust_phone,cust_identity,cust_email,cust_id from bypal_cust_info where cust_id=$cust_id";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function get_detail_by_id($cust_id){
        $sql="select id,cust_id,marry_status_id,communication_province,communication_city,communication_district,communication_address
        ,communication_postcode,education_status_id,degree_status_id,live_province,live_city,live_district,live_address,live_postcode,live_status,status,
        wechat_account from bypal_cust_detailinformation where cust_id=$cust_id";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function get_work_by_id($cust_id){
        $sql="select id,cust_id,current_status,company_email,profession_status_id,company_name,company_province,company_city,company_district,
        company_address,company_industry,duty_status,title_status,status from bypal_cust_workdetail where cust_id=$cust_id";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function get_linkman_by_id($cust_id){
        $sql="select id,cust_id,first_linkman_name,first_linkman_type,first_linkman_phone,first_linkman_province,first_linkman_city,first_linkman_district,
        first_linkman_address,second_linkman_name,second_linkman_type,second_linkman_phone,status from bypal_cust_linkman where cust_id=$cust_id";
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function select_cust_info($search_array){
        $sql="select t1.cust_id,t1.cust_name,t1.cust_loadname,t1.cust_phone,t1.cust_email,t2.fund_balance,t1.cust_recentdate,cust_login_ip
        from bypal_cust_info t1 left join bypal_balance_invest t2 on t1.cust_id=t2.cust_id ";
        $where="where 1=1 ";
        if($search_array['nameSelect'] !=''){
            $where.=" and t1.cust_name like '%".$search_array['nameSelect']."%' ";
        }
        if($search_array['phoneSelect'] !=''){
            $where.=" and t1.cust_phone ='".$search_array['phoneSelect']."' ";
        }
        if($search_array['emailSelect'] != ''){
            $where.=" and t1.cust_email='".$search_array['emailSelect']."'";
        }
        $offset=($search_array['page']-1)*$search_array['rows'];
        $order=" order by t1.cust_createdate desc";
        $limit=" limit $offset,".$search_array['rows'];
        $sql.=$where.$order.$limit;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function select_cust_info_count($search_array){
        $sql="select count(t1.cust_id) count
        from bypal_cust_info t1 left join bypal_balance_invest t2 on t1.cust_id=t2.cust_id ";
        $where="where 1=1 ";
        if($search_array['nameSelect'] !=''){
            $where.=" and t1.cust_name like '%".$search_array['nameSelect']."%' ";
        }
        if($search_array['phoneSelect'] !=''){
            $where.=" and t1.cust_phone ='".$search_array['phoneSelect']."' ";
        }
        if($search_array['emailSelect'] != ''){
            $where.=" and t1.cust_email='".$search_array['emailSelect']."'";
        }
        $sql.=$where;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $count=$values[0]["count"];
        return $count;
    }

    public function select_cust_information($cust_id){
        $sql="select t1.cust_id,t6.cust_typename,t1.cust_name,case when cust_sex=1 then '男' when cust_sex=2 then '女' else '' end cust_sex,
            t1.cust_phone,t1.cust_email,CONCAT(SUBSTR(cust_identity FROM 7 FOR 4),'/',SUBSTR(cust_identity FROM 11 FOR 2)) cust_birthday,
            t1.cust_identity,t7.m_name,concat(communication_province,communication_city,communication_district,communication_address) communication,
            t2.communication_postcode,t8.edu_name,t9.deg_name,FROM_UNIXTIME(t1.cust_createdate,'%Y-%m-%d') cust_createdate,t5.bc_bankname,t5.bc_bankcard,
            t10.first_linkman_name,t11.r_name first_linkman_type,CONCAT(t10.first_linkman_province,t10.first_linkman_city,t10.first_linkman_district,t10.first_linkman_address) linkmin,
            t10.first_linkman_phone,t10.second_linkman_name,t12.r_name second_linkman_type,t10.second_linkman_phone,t3.company_name,t3.company_email,t5.bc_reservedphone,
            t13.pos_name,t14.du_name,t15.ti_name,t16.name,concat(t2.live_province,t2.live_city,t2.live_district,t2.live_address) live,t2.live_postcode,
            t17.li_value,t1.cust_pic,t2.wechat_account,t3.school_name,t3.entrance_year,t18.learning_name,t19.system_name,t3.current_status,
            concat(t3.company_province,t3.company_city,t3.company_district,t3.company_address) company from bypal_cust_info t1
            left join bypal_cust_detailinformation t2 on t1.cust_id=t2.cust_id
            left join bypal_cust_marital t7 on t2.marry_status_id=t7.m_id
            left join bypal_cust_education t8 on t2.education_status_id=t8.edu_id
            left join bypal_cust_degree t9 on t2.degree_status_id=t9.deg_id
            left join bypal_cust_workdetail t3 on t1.cust_id=t3.cust_id
            left join bypal_cust_typename t6 on t3.current_status=t6.cust_typeid
            left join (select bc_bankname,bc_custid,bc_bankcard,bc_reservedphone from bypal_cust_bankcard where bc_bankstate=1) t5 on t1.cust_id=t5.bc_custid
            left join bypal_cust_linkman t10 on t1.cust_id=t10.cust_id
            left join bypal_cust_relation t11 on t10.first_linkman_type=t11.r_id
            left join bypal_cust_relation t12 on t10.second_linkman_type=t12.r_id
            left join bypal_cust_position t13 on t3.profession_status_id=t13.pos_id
            left join bypal_cust_duty t14 on t3.duty_status=t14.du_id
            left join bypal_cust_title t15 on t3.title_status=t15.ti_id
            left join bypal_profession_info t16 on t3.company_industry=t16.id
            left join bypal_cust_livestatus t17 on t2.live_status=t17.li_id
            left join bypal_cust_learning t18 on t3.learning_number=t18.learning_number
            left join bypal_cust_system t19 on t3.system_number=t19.system_number
            where t1.cust_id=".$cust_id;

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    public function select_cust_detail($cust_id){
        $sql="select t1.cust_name,t1.cust_identity,t1.cust_loadname,t1.cust_email,t1.cust_login_ip,t2.bc_bankcard,t1.cust_phone from bypal_cust_info t1
            left join bypal_cust_bankcard t2 on t1.cust_id=t2.bc_custid
            where t1.cust_id='".$cust_id."' and t2.bc_bankstate=1";

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values[0];
    }
}